package com.totoro.service;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.totoro.domain.DatePlace;
import com.totoro.domain.Degree;
import com.totoro.domain.Department;
import com.totoro.domain.Fidelity;
import com.totoro.domain.Major;
import com.totoro.domain.MySysRemarkTemplate;
import com.totoro.domain.ProfTitle;
import com.totoro.domain.Project;
import com.totoro.domain.ProjectCategory;
import com.totoro.domain.ProjectFrom;
import com.totoro.domain.ProjectType;
import com.totoro.domain.PublicReplyGroup;
import com.totoro.domain.Resource;
import com.totoro.domain.ReviewProject;
import com.totoro.domain.Role;
import com.totoro.domain.RoleResource;
import com.totoro.domain.School;
import com.totoro.domain.Staff;
import com.totoro.domain.Student;
import com.totoro.domain.StudentClass;
import com.totoro.domain.SysRemarkTemplate;
import com.totoro.domain.Tutorial;
import com.totoro.domain.User;
import com.totoro.domain.UserRole;
import com.totoro.util.ExcelToMysql;

public class ExcelToMysqlTest {
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	private String filePath;

	private SchoolService schoolService;

	private DepartmentService departmentService;

	private MajorService majorService;

	private StudentClassService studentClassService;

	private ProfTitleService profTitleService;
	private DegreeService degreeService;
	private PublicReplyGroupService publicReplyGroupService;
	private StaffService staffService;
	
	private ActorService actorService;
	
	private ProjectTypeService projectTypeService;
	private ProjectCategoryService projectCategoryService;
	private FidelityService  fidelityService;
	private ProjectFromService projectFromService;
	private ProjectService projectService;
	private TutorialService tutorialService;
	private ReviewProjectService reviewProjectService;
	
	
	private StudentService studentService;

	private UserService userService;

	private RoleService roleService;

	private ResourceService resourceService;

	private UserRoleService userRoleService;

	private RoleResourceService roleResourceService;
	private SysRemarkTemplateService sysRemarkTemplateService;
	private MySysRemarkTemplateService mySysRemarkTemplateService;

	@Before
	public void init() {
		ApplicationContext ctx = new ClassPathXmlApplicationContext(
				"classpath:applicationContext.xml");
		filePath = "D://Data.xls";
//		filePath = "e:/My Documents/教学/学生活动/totoro/数据准备/Data.xls";
		// filePath = "e:/My Documents/教学/学生活动/totoro/数据准备/Data.xls";

		schoolService = ctx.getBean(SchoolService.class);

		departmentService = ctx.getBean(DepartmentService.class);
		majorService = ctx.getBean(MajorService.class);
		studentClassService = ctx.getBean(StudentClassService.class);
		profTitleService = ctx.getBean(ProfTitleService.class);
		degreeService = ctx.getBean(DegreeService.class);
		publicReplyGroupService = ctx.getBean(PublicReplyGroupService.class);
		staffService = ctx.getBean(StaffService.class);
		
		projectTypeService = ctx.getBean(ProjectTypeService.class);
		projectCategoryService = ctx.getBean(ProjectCategoryService.class);
		fidelityService = ctx.getBean(FidelityService.class);
		projectFromService = ctx.getBean(ProjectFromService.class);
		projectService = ctx.getBean(ProjectService.class);
		tutorialService = ctx.getBean(TutorialService.class);
		
		studentService = ctx.getBean(StudentService.class);

		userService = ctx.getBean(UserService.class);
		roleService = ctx.getBean(RoleService.class);
		resourceService = ctx.getBean(ResourceService.class);
		userRoleService = ctx.getBean(UserRoleService.class);
		roleResourceService = ctx.getBean(RoleResourceService.class);
		reviewProjectService = ctx.getBean(ReviewProjectService.class);
		actorService = ctx.getBean(ActorService.class);
		sysRemarkTemplateService  = ctx.getBean(SysRemarkTemplateService.class);
		mySysRemarkTemplateService  = ctx.getBean(MySysRemarkTemplateService.class);
	}

	@Test
	public void testAll() throws ParseException {
		this.saveSchool();
		this.saveDepartment();
		this.saveMajor();
		this.saveStudentClass();
		
		this.saveProfTitle();
		this.saveDegree();
		this.savePublicReplyGroup();
		this.saveStaff();
		this.saveProjectType();
		this.saveProjectCategory();
		this.saveFidelity();
		this.saveProjectFrom();
		this.saveProject();

		this.saveStudent();
		this.saveTutorial();	
		this.saveUser();
		this.saveRole();
		this.saveUserRole();
		this.saveResource();
		this.saveRoleResource();		
		this.saveReviewProject();
		this.saveSysRemarkTemplateService();
		this.saveMySysRemarkTemplateService();
//		
	}
	
	
	public void saveSysRemarkTemplateService(){
		String sheetStr = "sys_remark_template";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			SysRemarkTemplate sysRemarkTemplate = new SysRemarkTemplate();
						
			sysRemarkTemplate.setDescription(val[1]);
			sysRemarkTemplate.setKeyword(val[2]);			

			sysRemarkTemplateService.save(sysRemarkTemplate);
		}
	}
	//
	
	public void saveMySysRemarkTemplateService(){
		String sheetStr = "my_sys_remark_template";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			MySysRemarkTemplate mySysRemarkTemplate = new MySysRemarkTemplate();
						
			mySysRemarkTemplate.setDescription(val[1]);
			mySysRemarkTemplate.setKeyword(val[2]);
			mySysRemarkTemplate.setActor(actorService.get(this.getIntId(val[3])));

			mySysRemarkTemplateService.save(mySysRemarkTemplate);
		}	
	}
	public void saveReviewProject() throws ParseException {
		String sheetStr = "review_project";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
//			this.outputArr(val);
			if(val[5].equals("null")||val[5].length()==0){
				continue;
			}
			System.out.println(val[5]+" 's size="+ val[5].length());
				
				ReviewProject reviewProject = new ReviewProject();
				reviewProject.setProject(projectService.get(this.getIntId(val[4])));			
						
				reviewProject.setReviewer(staffService.get(this.getIntId(val[5])));
				reviewProjectService.save(reviewProject);
			

			
			
		}
	}
	
	public void saveTutorial() throws ParseException {
		String sheetStr = "tutorial";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			Tutorial tutorial = new Tutorial();
			tutorial.setRank(this.getIntId(val[2]));
			tutorial.setProject(projectService.get(this.getIntId(val[6])));
			//应该 是tutorService?
			tutorial.setTutor(staffService.get(this.getIntId(val[7])));
			
			tutorialService.save(tutorial);
		}
	}

	
	
	public void saveProject() throws ParseException {
		String sheetStr = "project";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			Project project = new Project();
			project.setId(this.getIntId(val[0]));
			project.setTitle(val[1]);
			//保留小数点前面的文本
			String year = val[2];
				
			project.setYear(year.split("\\.")[0]);
			project.setFidelity(fidelityService.get(this.getIntId(val[3])));
			project.setProjectCategory(projectCategoryService.get(this.getIntId(val[4])));
			project.setProjectFrom(projectFromService.get(this.getIntId(val[5])));
			project.setProjectType(projectTypeService.get(this.getIntId(val[6])));
			project.setPublicReplyGroup(publicReplyGroupService.get(this.getIntId(val[7])));
			//proposer_id 命题老师
			project.setProposer(staffService.get(this.getIntId(val[8])));
			project.setPaperFileName(val[9].split("\\.")[0]);
			
			projectService.save(project);
		}
	}
	
	
	public void saveProjectFrom() throws ParseException {
		String sheetStr = "project_from";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			ProjectFrom projectFrom = new ProjectFrom();

			projectFrom.setDescription(val[0]);
			projectFrom.setNo(val[2]);	

			projectFromService.save(projectFrom);
		}
	}
	
	public void saveFidelity() throws ParseException {
		String sheetStr = "fidelity";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			Fidelity fidelity = new Fidelity();

			fidelity.setDescription(val[0]);
			fidelity.setNo(val[2]);	

			fidelityService.save(fidelity);
		}
	}
	
	public void saveProjectCategory() throws ParseException {
		String sheetStr = "project_category";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			ProjectCategory projectCategory = new ProjectCategory();

			projectCategory.setDescription(val[0]);
			projectCategory.setNo(val[2]);	

			projectCategoryService.save(projectCategory);
		}
	}
	
	
	public void saveProjectType() throws ParseException {
		String sheetStr = "project_type";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			ProjectType projectType = new ProjectType();

			projectType.setDescription(val[0]);
			projectType.setNo(val[2]);	

			projectTypeService.save(projectType);
		}
	}
	
	
	public void saveStaff() throws ParseException {
		String sheetStr = "staff";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			Staff staff = new Staff();

			staff.setName(val[0]);
			//保留小数点前面的文本
			staff.setQualifiedForYear(val[6].split("\\.")[0]);

			staff.setProfTitle(profTitleService.get(this.getIntId(val[7])));
			staff.setDegree(degreeService.get(this.getIntId(val[8])));
			staff.setDepartment(departmentService.get(this.getIntId(val[9])));
			staff.setPublicReplyGroup(publicReplyGroupService.get(this.getIntId(val[10])));
			staff.setNo(val[11]);			

			staffService.save(staff);
		}
	}
/**
 * @param String
 * @return Integer
*/	
	private Integer getIntId(String val) {
		Integer id = (int) (Double.parseDouble(val));
		return id;
	}

	public void savePublicReplyGroup() throws ParseException {
		String sheetStr = "public_reply_group";
		// 注意在Excel中日期必须以“文本”的方式存贮
		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			PublicReplyGroup publicReplyGroup = new PublicReplyGroup();

			DatePlace datePlace = new DatePlace();

			java.util.Date endDate = sdf.parse(val[1]);
			datePlace.setEndDate(new java.sql.Date(endDate.getTime()));
			// datePlace.setEndDate( new java.sql.Date(new Long(val[1])));
			datePlace.setRoom(val[2]);
			java.util.Date startDate = sdf.parse(val[3]);
			datePlace.setStartDate(new java.sql.Date(startDate.getTime()));
			// datePlace.setStartDate( new java.sql.Date(new Long(val[3])));

			publicReplyGroup.setDatePlace(datePlace);
			publicReplyGroup.setDescription(val[4]);
			publicReplyGroup.setYear(val[5]);
			Integer id = getIntId(val[6]);
			publicReplyGroup.setDepartment(departmentService.get(id));

			publicReplyGroupService.save(publicReplyGroup);
		}
	}

	public void saveDegree() {
		String sheetStr = "degree";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			this.outputArr(val);
			Degree degree = new Degree();
			degree.setDescription(val[0]);
			degree.setNo(val[2]);

			degreeService.save(degree);
		}
	}

	private void outputArr(Object[] objs) {
		for (Object object : objs) {
			System.out.print(object + "/");
		}
		System.out.println();
	}

	public void saveProfTitle() {
		String sheetStr = "prof_title";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			// System.out.println("val[4] = " + val[4]);
			ProfTitle profTitle = new ProfTitle();

			profTitle.setDescription(val[0]);
			profTitle.setNo(val[2]);

			profTitleService.save(profTitle);
		}
	}

	public void saveSchool() {

		String sheetStr = "school";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			School school = new School();
			school.setNo(val[0]);
			school.setDescription(val[1]);
			schoolService.save(school);
		}
	}

	public void saveDepartment() {
		String sheetStr = "department";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			Department department = new Department();
			School school = schoolService.get((int) Double.parseDouble(val[3]));

			department.setNo(val[1]);
			department.setDescription(val[2]);
			department.setSchool(school);

			departmentService.save(department);
		}
	}

	public void saveMajor() {
		String sheetStr = "major";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			Major major = new Major();
			Department department = departmentService.get((int) Double
					.parseDouble(val[3]));

			major.setNo(val[1]);
			major.setDescription(val[2]);
			major.setDepartment(department);

			majorService.save(major);
		}
	}

	public void saveStudentClass() {
		String sheetStr = "student_Class";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			StudentClass studentClass = new StudentClass();
			Major major = majorService.get((int) Double.parseDouble(val[3]));

			studentClass.setNo(val[1]);
			studentClass.setDescription(val[2]);
			studentClass.setMajor(major);
			studentClassService.save(studentClass);

		}
	}

	public void saveStudent() {
		String sheetStr = "student";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			System.out.println("val[4] = " + val[4]);
			Student student = new Student();
			StudentClass studentClass = studentClassService.get((int) Double
					.parseDouble(val[10]));

			student.setName(val[5]);
			student.setNo(val[6]);
			student.setProject(projectService.get(this.getIntId(val[9])));
			student.setStudentClass(studentClass);
			

			studentService.save(student);
		}
	}


	public void saveUser() {
		String sheetStr = "user";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			System.out.println("val[4] = " + val[4]);
			User user = new User();

			user.setEnableToLogin(Double.parseDouble(val[1]) == 1.0 ? true
					: false);
			user.setPassword(val[2]);
			user.setUsername(val[4]);
		
			user.setActor(actorService.get(this.getIntId(val[5])));

			userService.save(user);
		}
	}


	public void saveRole() {
		String sheetStr = "role";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			System.out.println("val[1] = " + val[1]);
			Role role = new Role();

			role.setDiscription(val[1]);
			role.setName(val[2]);
			role.setNo(val[3]);
			role.setRank((int) Double.parseDouble(val[4]));

			roleService.save(role);
		}
	}


	public void saveResource() {
		String sheetStr = "resource";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			System.out.println("val[1] = " + val[1]);
			System.out.println("val[2] = " + val[2]);
			System.out.println("val[3] = " + val[3]);
			Resource resource = new Resource();

			resource.setDiscription(val[1]);
			resource.setNo(val[2]);
			if (!val[3].equals("null")) {
				System.out.println("父id：" + ((int) Double.parseDouble(val[3])));
				Resource parent = resourceService.get((int) Double
						.parseDouble(val[3]));
				System.out.println("父resource：" + parent);
				resource.setParent(parent);
			}

			resourceService.save(resource);
		}
	}


	public void saveRoleResource() {
		String sheetStr = "role_resc";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);

		for (String[] val : list) {
			System.out.println("val[0] = " + val[0]);
			System.out.println("val[1] = " + val[1]);
			RoleResource roleResource = new RoleResource();

			roleResource.setRole(roleService.get((int) Double
					.parseDouble(val[0])));
			roleResource.setResource(resourceService.get((int) Double
					.parseDouble(val[1])));

			roleResourceService.save(roleResource);
		}
	}

	
	public void saveUserRole() {
		String sheetStr = "user_role";

		List<String[]> list = ExcelToMysql.excelToList(filePath, sheetStr);
		System.out.println(list.size());
		int counter = 1;
		for (String[] val : list) {
			System.out.println("NO.="+counter++);
			System.out.print("val[0] = " + val[0]);
			System.out.println("  val[1] = " + val[1]);
			UserRole userRole = new UserRole();

			userRole.setCreateDate(new Date(new java.util.Date().getTime()));
			User user = userService.get((int) Double.parseDouble(val[0]));
			userRole.setUser(user);
			Role role = roleService.get((int) Double.parseDouble(val[1]));
			userRole.setRole(role);
			System.out.println(user+"/"+role);
			userRoleService.save(userRole);
		}
		
	}
}
